use sqlx::{Row, Column, mysql::{MySqlPool, MySqlConnectOptions, MySqlSslMode}};
use super::{database::{DataBase, MyRow, ExecuteResult}, value_helper::mysql2lua};
use super::env::TOKIO;
use mlua::UserData;
use anyhow::Result;


impl MyRow for sqlx::mysql::MySqlRow{}

pub(crate) struct MySql{
    pool: Option<MySqlPool>
}


impl MySql {
    pub fn default() -> Self{
        Self { pool: None }
    }
}

impl DataBase for MySql{
    type Row = sqlx::mysql::MySqlRow;
    async fn connect(&mut self, host: Option<String>, port: Option<u16>, user: Option<String>, password: Option<String>, database: String, timeout: Option<u64>) -> Result<()> {
        println!("Building MySQL connection options...");
        let opt = MySqlConnectOptions::new()
                .host(host.as_deref().unwrap_or("localhost"))
                .port(port.unwrap_or(3306))
                .username(user.as_deref().unwrap_or("root"))
                .password(password.as_deref().unwrap_or("password"))
                .database(database.as_str())
                .ssl_mode(MySqlSslMode::Disabled)
                .timezone(Some(String::from("+08:00")));

        let connect_future = MySqlPool::connect_with(opt);
        match tokio::time::timeout(std::time::Duration::from_secs(timeout.unwrap_or(10)), connect_future).await {
            Ok(result) => {
                match result {
                    Ok(pool) => {
                        self.pool = Some(pool);
                        Ok(())
                    }
                    Err(e) => Err(e.into())
                }
            }
            Err(_) => Err(anyhow::anyhow!("Connection timed out"))
        }
    }
    async fn execute(&self, sql: String) -> Result<ExecuteResult> {
        if let Some(pool) = &self.pool{
            let res = sqlx::query(&sql).execute(pool).await?;
            Ok(ExecuteResult{
                rows_affected: res.rows_affected(),
                last_insert_id: Some(res.last_insert_id())
            })
        }else{
            Err(anyhow::anyhow!("Database not connected"))
        }
    }
    async fn query(&self, sql: String) -> Result<Vec<Self::Row>> {
        if let Some(pool) = &self.pool{
            let rows = sqlx::query(&sql).fetch_all(pool).await?;
            Ok(rows)
        }else{
            Err(anyhow::anyhow!("Database not connected"))
        }
    }
}

impl UserData for MySql{
    fn add_methods<M: mlua::UserDataMethods<Self>>(methods: &mut M) {
        methods.add_async_method_mut(
            "connect", 
            |_lua, 
                    mut this,
                    (host, port, user, password, database, timeout)
                    :(Option<String>, Option<u16>, Option<String>, Option<String>, String, Option<u64>)| async move{
            let _guard = TOKIO.enter();
            println!("Connecting to MySQL");
            this.connect(host, port, user, password, database, timeout).await.map_err(|e| mlua::Error::external(e))?;
            println!("Connected to MySQL");
            Ok(())
        });
        methods.add_async_method("execute", |lua, this, sql: String| async move{
            let _guard = TOKIO.enter();
            let res = this.execute(sql).await.map_err(|e| mlua::Error::external(e))?;
            let t = lua.create_table()?;
            t.set("rows_affected", res.rows_affected)?;
            t.set("last_insert_id", res.last_insert_id)?;
            Ok(t)
        });
        methods.add_async_method("query", |lua, this, sql: String| async move{
            let _guard = TOKIO.enter();
            let rows = this.query(sql).await.map_err(|e| mlua::Error::external(e))?;
            let lua_rows = lua.create_table()?;
            for (index, row) in rows.iter().enumerate(){
                let lua_row = lua.create_table()?;
                for(i, col) in row.columns().iter().enumerate(){
                    let value = mysql2lua(&lua, row, i, col.type_info())
                        .map_err(|e| mlua::Error::external(e))?;
                    lua_row.set(col.name(), value)?;
                }
                lua_rows.set(index+1, lua_row)?;
            }
            Ok(lua_rows)
        });
    }
}